<?php

// @TODO: Refactor
require_once(SITE_ROOT . 'config/constants.conf.php');
require_once(SITE_ROOT . 'class/db/DB.class.php');
require_once(SITE_ROOT . 'class/db/DBFlag.class.php');
require_once(SITE_ROOT . 'class/db/DBAllianceMember.class.php');
require_once(SITE_ROOT . 'class/bean/Team.class.php');
require_once(SITE_ROOT . 'class/bean/Flag.class.php');
require_once(SITE_ROOT . 'class/bean/League.class.php');
require_once(SITE_ROOT . 'class/bean/Division.class.php');
require_once(SITE_ROOT . 'class/bean/Country.class.php');

class DBTeam {

	private $params;
	
	private $select;
	private $from;
	
	public function __construct() {
		$this->reset();
		
		$this->select = "SELECT t.`team#`, t.`name`, t.`league#`, t.`update`, t.`away_coolness`, t.`away_flags`, t.`home_coolness`, t.`home_flags`, t.`and_coolness`, t.`and_flags`, t.`or_coolness`, t.`or_flags`, t.`plus_coolness`, t.`plus_flags`, t.`user#`, t.`username`, t.`activationdate`, t.`ntcoach`, t.`u20coach`";
		$this->from = " FROM `tteam` t";
	}
	
	public function getCountForHoC($countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate) {
		$db = new DB();
		
		$connection = $db->connect();
		
		$select = "SELECT count(*) AS numrows";
		
		if ($stmt = $connection->prepare($this->createStatement($select, null, null, null, null, $countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate))) {
			$count = 0;
			
			$stmt->execute($this->params);
			
			$stmt->bindColumn(1, $numrows);
			
			while ($stmt->fetch()) {
				$count = $numrows;
			}
			$this->reset();
			
			return $count;
		}
	}
	
	public function getTeamsForHoC($orderby, $countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate, $startindex, $endindex, $countrysort = null) {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		$select = $this->select . ", l.`name`, l.`division#`, l.`country#`";
		$from = $this->from . ", `tleague` l";
		$where = " WHERE t.`league#` = l.`league#`";
		$order = " ORDER BY";
		if ($countrysort === true) $order .= " l.`country#` ASC,";
		$order .= " t.`" . $orderby . "` DESC, t.`user#` DESC LIMIT " . $startindex . ", " . $endindex;
		$join = true;
		
		if ($stmt = $connection->prepare($this->createStatement($select, $from, $where, $order, $join, $countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate))) {
			$stmt->execute($this->params);
			
			$stmt->bindColumn(1, $teamID);
			$stmt->bindColumn(2, $name);
			$stmt->bindColumn(3, $leagueID);
			$stmt->bindColumn(4, $update);
			$stmt->bindColumn(5, $away_coolness);
			$stmt->bindColumn(6, $away_flags);
			$stmt->bindColumn(7, $home_coolness);
			$stmt->bindColumn(8, $home_flags);
			$stmt->bindColumn(9, $and_coolness);
			$stmt->bindColumn(10, $and_flags);
			$stmt->bindColumn(11, $or_coolness);
			$stmt->bindColumn(12, $or_flags);
			$stmt->bindColumn(13, $plus_coolness);
			$stmt->bindColumn(14, $plus_flags);
			$stmt->bindColumn(15, $userID);
			$stmt->bindColumn(16, $username);
			$stmt->bindColumn(17, $activationdate);
			$stmt->bindColumn(18, $ntcoach);
			$stmt->bindColumn(19, $u20coach);
			$stmt->bindColumn(20, $leaguename);
			$stmt->bindColumn(21, $divisionID);
			$stmt->bindColumn(22, $countryID);
					
			$teams = new ArrayObject();
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				
				$division = new Division();
				$division->setDivisionID($divisionID);
				$division->setCountry($country);
				
				$league = new League();
				$league->setLeagueID($leagueID);
				$league->setName($leaguename);
				$league->setDivision($division);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate, null, null, $ntcoach, $u20coach);
				//$team->setFlags($dbflag->getFlagsByTeam($team));
				$teams->append($team);
			}			
			$this->reset();
			
			return $teams;
		}
	}
	
	public function getHoCRank($score, $term, $orderby, $countryID = null, $divisionID = null, $leagueID = null, $allianceID = null, $fromdate = null, $todate = null) {
		$db = new DB();
		$connection = $db->connect();
		$select = "SELECT COUNT(*) AS `position`";
		$where = " WHERE t.`" . $orderby . "` > ?";
		
		$this->reset();
		$this->params[] = $score;
		if ($stmt = $connection->prepare($this->createStatement($select, null, $where, null, null, $countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate))) {
			$stmt->execute($this->params);
			
			$stmt->bindColumn(1, $position);
			
			while ($stmt->fetch()) {
				$rank = $position + 1;
			}			
			$this->reset();
			
			return $rank;
		}
	}
	
	public function getTeams() {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare($this->createStatement())) {
			$stmt->execute();
			
			$stmt->bindColumn(1, $teamID);
			$stmt->bindColumn(2, $name);
			$stmt->bindColumn(3, $leagueID);
			$stmt->bindColumn(4, $update);
			$stmt->bindColumn(5, $away_coolness);
			$stmt->bindColumn(6, $away_flags);
			$stmt->bindColumn(7, $home_coolness);
			$stmt->bindColumn(8, $home_flags);
			$stmt->bindColumn(9, $and_coolness);
			$stmt->bindColumn(10, $and_flags);
			$stmt->bindColumn(11, $or_coolness);
			$stmt->bindColumn(12, $or_flags);
			$stmt->bindColumn(13, $plus_coolness);
			$stmt->bindColumn(14, $plus_flags);
			$stmt->bindColumn(15, $userID);
			$stmt->bindColumn(16, $username);
			$stmt->bindColumn(17, $activationdate);
			$stmt->bindColumn(18, $ntcoach);
			$stmt->bindColumn(19, $u20coach);
			
			$teams = new ArrayObject();
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				
				$division = new Division();
				$division->setDivisionID($divisionID);
				$division->setCountry($country);
				
				$league = new League();
				$league->setLeagueID($leagueID);
				$league->setName($leaguename);
				$league->setDivision($division);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate, null, null, $ntcoach, $u20coach);
				//$team->setFlags($dbflag->getFlagsByTeam($team));
				$teams->append($team);
			}			
			$this->reset();
			
			return $teams;
		}
	}
	
	public function getTeamsByLeague($league) {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		$where = " WHERE t.`league#` = ?";
		
		if ($stmt = $connection->prepare($this->createStatement(null, null, $where))) {
			$stmt->execute(array($league->getLeagueID()));
			
			$stmt->bindColumn(1, $teamID);
			$stmt->bindColumn(2, $name);
			$stmt->bindColumn(3, $leagueID);
			$stmt->bindColumn(4, $update);
			$stmt->bindColumn(5, $away_coolness);
			$stmt->bindColumn(6, $away_flags);
			$stmt->bindColumn(7, $home_coolness);
			$stmt->bindColumn(8, $home_flags);
			$stmt->bindColumn(9, $and_coolness);
			$stmt->bindColumn(10, $and_flags);
			$stmt->bindColumn(11, $or_coolness);
			$stmt->bindColumn(12, $or_flags);
			$stmt->bindColumn(13, $plus_coolness);
			$stmt->bindColumn(14, $plus_flags);
			$stmt->bindColumn(15, $userID);
			$stmt->bindColumn(16, $username);
			$stmt->bindColumn(17, $activationdate);
			$stmt->bindColumn(18, $ntcoach);
			$stmt->bindColumn(19, $u20coach);
			
			$teams = new ArrayObject();
			
			while ($stmt->fetch()) {				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate, null, null, $ntcoach, $u20coach);
				//$team->setFlags($dbflag->getFlagsByTeam($team));
				$teams->append($team);
			}			
			$this->reset();
			
			return $teams;
		}
	}
	
	public function getTeamByID($id) {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		$where = " WHERE t.`team#` = ?";
		
		if ($stmt = $connection->prepare($this->createStatement(null, null, $where))) {
			$stmt->execute(array($id));
			
			$stmt->bindColumn(1, $teamID);
			$stmt->bindColumn(2, $name);
			$stmt->bindColumn(3, $leagueID);
			$stmt->bindColumn(4, $update);
			$stmt->bindColumn(5, $away_coolness);
			$stmt->bindColumn(6, $away_flags);
			$stmt->bindColumn(7, $home_coolness);
			$stmt->bindColumn(8, $home_flags);
			$stmt->bindColumn(9, $and_coolness);
			$stmt->bindColumn(10, $and_flags);
			$stmt->bindColumn(11, $or_coolness);
			$stmt->bindColumn(12, $or_flags);
			$stmt->bindColumn(13, $plus_coolness);
			$stmt->bindColumn(14, $plus_flags);
			$stmt->bindColumn(15, $userID);
			$stmt->bindColumn(16, $username);
			$stmt->bindColumn(17, $activationdate);
			$stmt->bindColumn(18, $ntcoach);
			$stmt->bindColumn(19, $u20coach);
			
			$team = null;
			
			while ($stmt->fetch()) {
				$league = new League();
				$league->setLeagueID($leagueID);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate, null, null, $ntcoach, $u20coach);
				$team->setFlags($dbflag->getFlagsByTeam($team));
			}			
			$this->reset();
			
			return $team;
		}
	}
	
	public function getTeamByUserID($userID) {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		$where = " WHERE t.`user#` = ? ORDER BY t.`activationdate` DESC";
		
		if ($stmt = $connection->prepare($this->createStatement(null, null, $where))) {
			$stmt->execute(array($userID));
			
			$stmt->bindColumn(1, $teamID);
			$stmt->bindColumn(2, $name);
			$stmt->bindColumn(3, $leagueID);
			$stmt->bindColumn(4, $update);
			$stmt->bindColumn(5, $away_coolness);
			$stmt->bindColumn(6, $away_flags);
			$stmt->bindColumn(7, $home_coolness);
			$stmt->bindColumn(8, $home_flags);
			$stmt->bindColumn(9, $and_coolness);
			$stmt->bindColumn(10, $and_flags);
			$stmt->bindColumn(11, $or_coolness);
			$stmt->bindColumn(12, $or_flags);
			$stmt->bindColumn(13, $plus_coolness);
			$stmt->bindColumn(14, $plus_flags);
			$stmt->bindColumn(15, $userID);
			$stmt->bindColumn(16, $username);
			$stmt->bindColumn(17, $activationdate);
			$stmt->bindColumn(18, $ntcoach);
			$stmt->bindColumn(19, $u20coach);
			
			$team = null;
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				
				$division = new Division();
				$division->setDivisionID($divisionID);
				$division->setCountry($country);
				
				$league = new League();
				$league->setLeagueID($leagueID);
				$league->setName($leaguename);
				$league->setDivision($division);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate, null, null, $ntcoach, $u20coach);
				//$team->setFlags($dbflag->getFlagsByTeam($team));
			}			
			$this->reset();
			
			return $team;
		}
	}
	
	public function saveTeams($teams) {
		$db = new DB();
		$dbflag = new DBFlag();
		$dballiancemember = new DBAllianceMember();
		
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('INSERT INTO `tteam` (`team#`, `name`, `league#`, `update`, `away_coolness`, `away_flags`, `home_coolness`, `home_flags`, `and_coolness`, `and_flags`, `or_coolness`, `or_flags`, `plus_coolness`, `plus_flags`, `user#`, `username`, `activationdate`, `ntcoach`, `u20coach`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `league#`=VALUES(`league#`), `name`=VALUES(`name`), `update`=VALUES(`update`), `away_coolness`=VALUES(`away_coolness`), `away_flags`=VALUES(`away_flags`), `home_coolness`=VALUES(`home_coolness`), `home_flags`=VALUES(`home_flags`), `and_coolness`=VALUES(`and_coolness`), `and_flags`=VALUES(`and_flags`), `or_coolness`=VALUES(`or_coolness`), `or_flags`=VALUES(`or_flags`), `plus_coolness`=VALUES(`plus_coolness`), `plus_flags`=VALUES(`plus_flags`), `user#`=VALUES(`user#`), `username`=VALUES(`username`), `activationdate`=VALUES(`activationdate`), `ntcoach`=VALUES(`ntcoach`), `u20coach`=VALUES(`u20coach`)')) {
			foreach ($teams as $team) {
				$teamID = $team->getTeamID();
				$name = $team->getName();
				$leagueID = $team->getLeague()->getLeagueID();
				$update = date('Y-m-d H:i:s', $team->getUpdate());
				$away_coolness = $team->getAwayCoolness();
				$away_flags = $team->getAwayFlags();
				$home_coolness = $team->getHomeCoolness();
				$home_flags = $team->getHomeFlags();
				$and_coolness = $team->getAndCoolness();
				$and_flags = $team->getAndFlags();
				$or_coolness = $team->getOrCoolness();
				$or_flags = $team->getOrFlags();
				$plus_coolness = $team->getPlusCoolness();
				$plus_flags = $team->getPlusFlags();
				$userID = $team->getUserID();
				$username = $team->getUsername();
				$activationdate = $team->getActivationDate();
				$ntcoach = $team->isNTCoach();
				$u20coach = $team->isU20Coach();
								
				$stmt->execute(array($teamID, $name, $leagueID, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate, $ntcoach, $u20coach));
				
				$flags = $team->getFlags();
				if (isset($flags)) {
					$dbflag->saveFlags($team);
				}
				
				$alliances = $team->getAlliances();
				if (isset($alliances)) {
					$dballiancemember->saveAllianceMembership($team);
				}
				
			}
		}
	}
	
	public function saveTeam($team) {
		$db = new DB();
		$dbflag = new DBFlag();
		$dballiancemember = new DBAllianceMember();
		
		$connection = $db->connect();
				
		if ($stmt = $connection->prepare('INSERT INTO `tteam` (`team#`, `name`, `league#`, `update`, `away_coolness`, `away_flags`, `home_coolness`, `home_flags`, `and_coolness`, `and_flags`, `or_coolness`, `or_flags`, `plus_coolness`, `plus_flags`, `user#`, `username`, `activationdate`, `ntcoach`, `u20coach`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `league#`=VALUES(`league#`), `name`=VALUES(`name`), `update`=VALUES(`update`), `away_coolness`=VALUES(`away_coolness`), `away_flags`=VALUES(`away_flags`), `home_coolness`=VALUES(`home_coolness`), `home_flags`=VALUES(`home_flags`), `and_coolness`=VALUES(`and_coolness`), `and_flags`=VALUES(`and_flags`), `or_coolness`=VALUES(`or_coolness`), `or_flags`=VALUES(`or_flags`), `plus_coolness`=VALUES(`plus_coolness`), `plus_flags`=VALUES(`plus_flags`), `user#`=VALUES(`user#`), `username`=VALUES(`username`), `activationdate`=VALUES(`activationdate`), `ntcoach`=VALUES(`ntcoach`), `u20coach`=VALUES(`u20coach`)')) {
			$teamID = $team->getTeamID();
			$name = $team->getName();
			$leagueID = $team->getLeague()->getLeagueID();
			$update = date('Y-m-d H:i:s', $team->getUpdate());
			$away_coolness = $team->getAwayCoolness();
			$away_flags = $team->getAwayFlags();
			$home_coolness = $team->getHomeCoolness();
			$home_flags = $team->getHomeFlags();
			$and_coolness = $team->getAndCoolness();
			$and_flags = $team->getAndFlags();
			$or_coolness = $team->getOrCoolness();
			$or_flags = $team->getOrFlags();
			$plus_coolness = $team->getPlusCoolness();
			$plus_flags = $team->getPlusFlags();
			$userID = $team->getUserID();
			$username = $team->getUsername();
			$activationdate = $team->getActivationDate();
			$ntcoach = $team->isNTCoach();
			$u20coach = $team->isU20Coach();
			
			$stmt->execute(array($teamID, $name, $leagueID, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate, $ntcoach, $u20coach));
			
			$flags = $team->getFlags();
			if (isset($flags)) {
				$dbflag->saveFlags($team);
			}
			
			$alliances = $team->getAlliances();
			if (isset($alliances)) {
				$dballiancemember->saveAllianceMembership($team);
			}
		}
	}
	
	public function deleteTeamByID($id) {
		$db = new DB();		
		$connection = $db->connect();
				
		if ($stmt = $connection->prepare('DELETE FROM `tteam` WHERE `team#` = ?')) {
			$stmt->execute(array($id));
		}
	}
	
	public function getLooseTeams() {
		$db = new DB();
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare("SELECT DISTINCT `team#` FROM `tflag` WHERE `team#` NOT IN (SELECT `team#` FROM `tteam`)")) {
			$stmt->execute();
			
			$stmt->bindColumn(1, $teamID);
			
			$teams = new ArrayObject();
			
			while ($stmt->fetch()) {
				$team = new Team($teamID);
				$teams->append($team);
			}
			return $teams;
		}
	}
	
	private function createStatement($select = null, $from = null, $where = null, $order = null, $join = null, $countryID = null, $divisionID = null, $leagueID = null, $allianceID = null, $fromdate = null, $todate = null) {
		
		if ($select == null) $select = $this->select;
		if ($from == null) $from = $this->from;
		
		if ($where == null) $whereCount = 0;
		else $whereCount = 1;
		
		if ($leagueID != null || $countryID != null || $divisionID != null) {
			if ($join != true) {
				$from .= ", `tleague` l";
				if ($whereCount > 0) $where .= " AND";
				else $where = " WHERE";
				$where .= " t.`league#` = l.`league#`";
				$whereCount++;
			}
			
			if ($leagueID != null) {
				$where .= " AND l.`league#`= ?";
				$this->params[] = $leagueID;
			}
			else if ($countryID != null && $divisionID != null) {
				$where .= " AND l.`country#` = ? AND l.`division#` = ?";
				$this->params[] = $countryID;
				$this->params[] = $divisionID;
			}
			else if ($countryID != null) {
				$where .= " AND l.`country#` = ?";
				$this->params[] = $countryID;
			}
			else if ($divisionID != null) {
				$where .= " AND l.`division#` = ?";
				$this->params[] = $divisionID;
			}
		}
		
		if ($allianceID != null) {
			$from .= ", `talliancemember` am";
			if ($whereCount > 0) $where .= " AND";
			else $where = " WHERE";
			$where .= " t.`team#` = am.`team#` AND am.`alliance#` = ?";
			$whereCount++;
			$this->params[] = $allianceID;
		}
		
		if ($fromdate != null) {
			if ($whereCount > 0) $where .= " AND";
			else $where = " WHERE";
			$where .= " t.`activationdate` >= ?";
			$whereCount++;
			$this->params[] = date("Y-m-d", $fromdate);			
		}
		
		if ($todate != null) {
			if ($whereCount > 0) $where .= " AND";
			else $where = " WHERE";
			$where .= " t.`activationdate` < ?";
			$whereCount++;
			$this->params[] = date("Y-m-d", $todate);			
		}
		//echo $select . $from . $where . $order;
		return $select . $from . $where . $order;
	}
	
	private function reset() {
		$this->params = array();
	}
}

?>